package com.aaa.dao;

import com.aaa.entity.User;
import com.aaa.entity.UserRoleVO;
import com.mysql.jdbc.ConnectionImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据用户名和密码查询用户对象
     * @param user
     * @return
     */
    public User finByUsernameAndPassword(User user) throws Exception{
        String sql ="select * from user where username=? and password =?";
        User backgroundUser = this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),user.getUsername(),user.getPassword());
        return backgroundUser;
    }
    /**
     * 根据id查询用户对象
     * @param uid
     * @return
     */
    public User byUid(Long uid){
        String sql ="select * from user where uid=?";
        User backgroundUser = this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),uid);
        return backgroundUser;
    }
    //根据uid修改用户密码头像
    public Integer update(User user){
        String sql = "update user set password=?,Avatar=? where uid=?";
        int update = this.jdbcTemplate.update(sql, user.getPassword(), user.getAvatar(), user.getUid());
        return update;
    }
    //根据uid修改用户职位
    public Integer updateRole(User user){
        String sql = "update user set password=?,Avatar=?,idcard=?,sex=?,phone=?where uid=?";
        int update = this.jdbcTemplate.update(sql, user.getPassword(), user.getAvatar(), user.getUid());
        return update;
    }
    //查询员工信息及角色
    public List<UserRoleVO> listAll(String uname,String sex,String name,Integer offset, Integer limit){
        String sql = "select uid,username,password,avatar,idcard,uname,sex,phone,entrytime,id,name from user_role ur join user u on ur.u_id = u.uid join role r on ur.r_id = r.id where 1=1 ";
        if(uname!=null&&uname.trim().length()>0){
            sql+=" and uname like '%"+uname+"%'" ;
        }

        if(sex!=null&&sex.trim().length()>0){
            sql+=" and sex ='"+sex+"'" ;
        }
        if(name!=null&&name.trim().length()>0){
            sql+=" and name like  '"+name+"'" ;
        }
        sql+= " limit ?,?";
        //System.out.println(sql);
        List<UserRoleVO> query = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserRoleVO>(UserRoleVO.class),offset , limit );
        return query;
    }
    //查询员工信息总数
    public Integer  numberOfEmployees(){
        String sql = "select count(uid) from user";
        Integer integer = this.jdbcTemplate.queryForObject(sql, Integer.class);
        return integer;
    }
    //查询职位
    public List<UserRoleVO> listAllRole(){
        String sql = "select id,name from role";
        List<UserRoleVO> result = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserRoleVO>(UserRoleVO.class));
        return result;
    }
    //添加用户
    public Integer save(User user)   {
        String sql = "insert into user (uname,username,password,Avatar,idcard,sex,phone,entrytime)  values(?,?,?,?,?,?,?,?)";
        int update = this.jdbcTemplate.update(sql,user.getUname(), user.getUsername(), user.getPassword(), user.getAvatar(), user.getIdcard(), user.getSex(), user.getPhone(), user.getEntrytime());
        return update;
    }
    //添加用户/角色中间表
    public Integer saveUserRole(Long u_id,Long r_id){
        String sql = "insert into user_role(u_id,r_id) values(?,?)";
        int update = this.jdbcTemplate.update(sql, u_id, r_id);
        return update;
    }
    /**
     * 删除用户角色
     * @param uid
     * @return
     */
    public Integer deleteUserRoles(Long uid){
        String sql = "delete from user_role  where u_id=?";
        int count = this.jdbcTemplate.update(sql, uid);
        return count;
    }

    /**
     * 根据用户名和密码查询用户id
     * @param
     * @return
     */
    public User QueryUid(String username, String password) {
        String sql ="select uid from user where username=? and password =?";
        User backgroundUser = this.jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class),username,password);
        return backgroundUser;
    }
    public Integer countByUsername(String username){
        String sql="select count(uid) as c from user where username=?";
        Integer integer = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        }, username);
        return integer;
    }
    public Integer countByUsernameId(String username,Integer uid){
        String sql="select count(uid) as c from user where username=? and uid !=?";
        Integer integer = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        }, username, uid);
        return integer;
    }
}
